#!/usr/bin/env python
# coding:utf-8


import logging
from datetime import date
from utils.mysqlutils import MysqlUtils
from datatabletransfer import DataTableTransfer
from fieldsmap import *


class FundPriceDay(DataTableTransfer):
    """
    基金历史日交易(TQ_QT_FDQUOTE) --> 基金日行情(fund_price_day)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,SYMBOL as sec_code, TRADEDATE as trade_date  from TQ_QT_FDQUOTE;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,f.sec_code, replace(f.trade_date,'-','') as trade_date
    from fund_price_day f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,SYMBOL,SENAME,TRADEDATE,`EXCHANGE`,LCLOSE,TOPEN,
    # TCLOSE,THIGH,TLOW,VOL,AMOUNT,DEALS,AVGPRICE,AVGVOL,AVGTRAMT,`CHANGE`,PCHG,AMPLITUDE,TURNRATE,DISCOUNTRATE
    # from TQ_QT_FDQUOTE limit %s, 1000;''')

    TABLE = 'fund_price_day'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'sec_code':False, 'trade_date':False}
    COLUMN_ID = 'id'  # 更新主键

    DEFAULT_VALUE = {
        'pre_close_price': None,
        'open_price': None,
        'close_price': None,
        'high_price': None,
        'low_price': None,
        'volume': None,
        'amount': None,
        'trade_num': None,
        'avg_price': None,
        'avg_vol': None,
        'avg_amount': None,
        'differ': None,
        'differ_range': None,
        'amplitude': None,
        'turnover_rate': None,
        'discount_rate': None,
    }
    DATETIME = {
        'trade_date': None,
    }

    MAP_FIELDS = FundPriceDayMap
    MAP_VALUES = FundPriceDayValueMap


class FundPriceWeek(DataTableTransfer):
    """
    基金历史周交易(TQ_QT_FDWQUOTE) --> 基金周行情(fund_price_week)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,TRADEDATE as trade_date  from TQ_QT_FDWQUOTE;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.trade_date,'-','') as trade_date
           from fund_price_week f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,SYMBOL,TRADEDATE,`EXCHANGE`,LCLOSE,TOPEN, HIGH,LOW,
    # TCLOSE,HTCLOSE,LTCLOSE, AVGPRICE,CHG,PCHG,AVGPCHG,TURNOVER,AVGTURNOVER,VOTURNOVER,VATURNOVER,PRANGE,AVGDISCOUNTW,
    # AVGDISCOUNTRATEW, HTRADEDATE,LTRADEDATE,HTTRADEDATE,LTTRADEDATE from TQ_QT_FDWQUOTE limit %s, 1000;''')

    TABLE = 'fund_price_week'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'trade_date':False}
    COLUMN_ID = 'id'  # 更新主键

    DEFAULT_VALUE = {
        'pre_close_price': None,
        'open_price': None,
        'close_price': None,
        'high_price': None,
        'low_price': None,
        'high_close_price': None,
        'low_close_price': None,
        'avg_price': None,
        'differ': None,
        'differ_range': None,
        'avg_differ_range': None,
        'turnover_rate': None,
        'avg_turnover_rate': None,
        'volume': None,
        'amount': None,
        'amplitude': None,
        'avg_discount': None,
        'avg_discount_rate': None,
    }
    DATETIME = {
        'trade_date': None,
    }

    MAP_FIELDS = FundPriceWeekMap
    MAP_VALUES = FundPriceWeekValueMap

    @classmethod
    def upset_mysql(cls, table, rows):
        """
        导入数据至中心库, 跨月的一周会保留两条记录
        20180308: 取消跨月的一周会保留两条记录
        :param table:
        :param rows:
        :return:
        """

        for index, row in enumerate(rows):
            source_value, trade_date = list(cls.find_record(table, row, cls.SOURCE_ID)) \
                if cls.SOURCE_ID else [None, None]
            if source_value and trade_date.strftime('%Y%m%d') == row['TRADEDATE']:
                continue

            if trade_date:
                today = date(int(row['TRADEDATE'][:4]), int(row['TRADEDATE'][4:6]), int(row['TRADEDATE'][6:]))
                week_today = today.strftime('%w')
                week_before = trade_date.strftime('%w')

                # 跨月的一周会保留两条记录, 且替换时间不能超过7天
                # if source_value and int(row['TRADEDATE'][4:6]) == trade_date.month and (today-trade_date).days < 7 \
                #         and week_before < week_today:
                if source_value and (today-trade_date).days < 7 and week_before < week_today:
                    cls.upset(table=table, row=row, column_value=source_value, upset=True)
                else:
                    cls.upset(table=table, row=row, column_value=None, upset=False)
            else:
                cls.upset(table=table, row=row, column_value=None, upset=False)

    @classmethod
    def find_record(cls, table, row, columns=tuple()):
        """
        取相对日期最近一周的最新记录
        :param table:
        :param row:
        :param columns:
        :return:
        """

        values = list()
        for index, column in enumerate(columns):
            if column in cls.MAP_VALUES.keys():
                map_value = cls.MAP_VALUES[column]
                if map_value.keys()[0] == 'SQL':  # 从其他表获取数据
                    conf = map_value.values()[0][0]
                    query = map_value.values()[0][1]
                    default_value = map_value.values()[0][2]
                    value = cls.exec_sql(conf, query, (row[cls.MAP_FIELDS[column]], ), default_value)
                else:
                    value = map_value.get(row[cls.MAP_FIELDS[column]], None)
            else:     # 直接取值
                value = row[cls.MAP_FIELDS[column]]
            values.append(value)

        conn = MysqlUtils.connect(cls.data_center)
        query_sql = ('''select {1}, trade_date from {0} where sec_uni_code=%s and trade_date<=%s
                order by trade_date desc limit 1;'''.format(table, cls.COLUMN_ID))
        cursor = MysqlUtils.common_query(conn=conn, query=query_sql, args=tuple(values))
        if cursor and cursor.rowcount:
            row = cursor.fetchone()
            cursor.close()
        else:
            row = None, None
        conn.close()
        return row


class FundPriceMonth(DataTableTransfer):
    """
    基金历史月交易(TQ_QT_FDMQUOTE) --> 基金月行情(fund_price_month)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,TRADEDATE as trade_date  from TQ_QT_FDMQUOTE;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.trade_date,'-','') as trade_date
              from fund_price_month f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,SYMBOL,TRADEDATE,`EXCHANGE`,LCLOSE,TOPEN,TCLOSE,HIGH,
    # LOW,HTCLOSE,LTCLOSE, AVGPRICE,CHG,PCHG,AVGPCHG,TURNOVER,AVGTURNOVER,VOTURNOVER,VATURNOVER,PRANGE,AVGDISCOUNTM,
    # AVGDISCOUNTRATEM, HTRADEDATE,LTRADEDATE,HTTRADEDATE,LTTRADEDATE from TQ_QT_FDMQUOTE limit %s, 1000;''')

    TABLE = 'fund_price_month'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'trade_date':False}
    COLUMN_ID = 'id'  # 更新主键

    DEFAULT_VALUE = {
        'pre_close_price': None,
        'open_price': None,
        'close_price': None,
        'high_price': None,
        'low_price': None,
        'high_close_price': None,
        'low_close_price': None,
        'avg_price': None,
        'differ': None,
        'differ_range': None,
        'avg_differ_range': None,
        'turnover_rate': None,
        'avg_turnover_rate': None,
        'volume': None,
        'amount': None,
        'amplitude': None,
        'avg_discount': None,
        'avg_discount_rate': None,
        'high_price_date': None,
        'low_price_date': None,
        'high_close_price_date': None,
        'low_close_price_date': None,
    }
    DATETIME = {
        'trade_date': None,
    }

    MAP_FIELDS = FundPriceMonthMap
    MAP_VALUES = FundPriceMonthValueMap

    @classmethod
    def upset_mysql(cls, table, rows):
        """
        导入数据至中心库
        :param table:
        :param rows:
        :return:
        """

        for index, row in enumerate(rows):
            source_value, trade_date = list(cls.find_record(table, row, cls.SOURCE_ID)) \
                if cls.SOURCE_ID else [None, None]
            if source_value and trade_date.strftime('%Y%m%d') == row['TRADEDATE']:
                continue

            if source_value and trade_date.strftime('%Y%m') == row['TRADEDATE'][:6]:
                cls.upset(table=table, row=row, column_value=source_value, upset=True)
            else:
                cls.upset(table=table, row=row, column_value=None, upset=False)

    @classmethod
    def find_record(cls, table, row, columns=tuple()):
        """
        取相对日期最近一月的最新记录
        :param table:
        :param row:
        :param columns:
        :return:
        """

        values = list()
        for index, column in enumerate(columns):
            if column in cls.MAP_VALUES.keys():
                map_value = cls.MAP_VALUES[column]
                if map_value.keys()[0] == 'SQL':  # 从其他表获取数据
                    conf = map_value.values()[0][0]
                    query = map_value.values()[0][1]
                    default_value = map_value.values()[0][2]
                    value = cls.exec_sql(conf, query, (row[cls.MAP_FIELDS[column]], ), default_value)
                else:
                    value = map_value.get(row[cls.MAP_FIELDS[column]], None)
            else:     # 直接取值
                value = '{0}-{1}'.format(row[cls.MAP_FIELDS[column]][:4], row[cls.MAP_FIELDS[column]][4:6]) \
                    if column == 'trade_date' else row[cls.MAP_FIELDS[column]]
            values.append(value)

        conn = MysqlUtils.connect(cls.data_center)
        query_sql = ('''select {1}, trade_date from {0} where sec_uni_code=%s and left(trade_date,7)=%s
                order by trade_date desc limit 1;'''.format(table, cls.COLUMN_ID))
        cursor = MysqlUtils.common_query(conn=conn, query=query_sql, args=tuple(values))
        if cursor and cursor.rowcount:
            row = cursor.fetchone()
            cursor.close()
        else:
            row = None, None
        conn.close()
        return row


class FundPriceYear(DataTableTransfer):
    """
    基金历史年交易(TQ_QT_FDYQUOTE) --> 基金年行情(fund_price_year)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,TRADEDATE as trade_date  from TQ_QT_FDYQUOTE;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.trade_date,'-','') as trade_date
               from fund_price_year f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,SYMBOL,TRADEDATE,`EXCHANGE`,LCLOSE,TOPEN,TCLOSE,HIGH,
    # LOW,HTCLOSE,LTCLOSE,AVGPRICE,CHG,PCHG,AVGPCHG,TURNOVER,AVGTURNOVER,VOTURNOVER,VATURNOVER,PRANGE,AVGDISCOUNTY,
    # AVGDISCOUNTRATEY, HTRADEDATE,LTRADEDATE,HTTRADEDATE,LTTRADEDATE from TQ_QT_FDYQUOTE limit %s, 1000;''')

    TABLE = 'fund_price_year'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'trade_date':False}
    COLUMN_ID = 'id'  # 更新主键

    DEFAULT_VALUE = {
        'pre_close_price': None,
        'open_price': None,
        'close_price': None,
        'high_price': None,
        'low_price': None,
        'high_close_price': None,
        'low_close_price': None,
        'avg_price': None,
        'differ': None,
        'differ_range': None,
        'avg_differ_range': None,
        'turnover_rate': None,
        'avg_turnover_rate': None,
        'volume': None,
        'amount': None,
        'amplitude': None,
        'avg_discount': None,
        'avg_discount_rate': None,
    }
    DATETIME = {
        'trade_date': None,
    }

    MAP_FIELDS = FundPriceYearMap
    MAP_VALUES = FundPriceYearValueMap

    @classmethod
    def upset_mysql(cls, table, rows):
        """
        导入数据至中心库
        :param table:
        :param rows:
        :return:
        """

        for index, row in enumerate(rows):
            source_value, trade_date = list(cls.find_record(table, row, cls.SOURCE_ID)) \
                if cls.SOURCE_ID else [None, None]
            if source_value and trade_date.strftime('%Y%m%d') == row['TRADEDATE']:
                continue

            if source_value and trade_date.year == int(row['TRADEDATE'][:4]):
                cls.upset(table=table, row=row, column_value=source_value, upset=True)
            else:
                cls.upset(table=table, row=row, column_value=None, upset=False)

    @classmethod
    def find_record(cls, table, row, columns=tuple()):
        """
        取相对日期最近一年的最新记录
        :param table:
        :param row:
        :param columns:
        :return:
        """

        values = list()
        for index, column in enumerate(columns):
            if column in cls.MAP_VALUES.keys():
                map_value = cls.MAP_VALUES[column]
                if map_value.keys()[0] == 'SQL':  # 从其他表获取数据
                    conf = map_value.values()[0][0]
                    query = map_value.values()[0][1]
                    default_value = map_value.values()[0][2]
                    value = cls.exec_sql(conf, query, (row[cls.MAP_FIELDS[column]], ), default_value)
                else:
                    value = map_value.get(row[cls.MAP_FIELDS[column]], None)
            else:     # 直接取值
                value = row[cls.MAP_FIELDS[column]][:4] if column == 'trade_date' else row[cls.MAP_FIELDS[column]]
            values.append(value)

        conn = MysqlUtils.connect(cls.data_center)
        query_sql = ('''select {1}, trade_date from {0} where sec_uni_code=%s and left(trade_date,4)=%s
                order by trade_date desc limit 1;'''.format(table, cls.COLUMN_ID))
        cursor = MysqlUtils.common_query(conn=conn, query=query_sql, args=tuple(values))
        if cursor and cursor.rowcount:
            row = cursor.fetchone()
            cursor.close()
        else:
            row = None, None
        conn.close()
        return row


if __name__ == '__main__':

    log_path = '../logs/export_fund_market_server.log'
    logging.basicConfig(filename=log_path,
                        format='%(asctime)s %(name)s %(levelname)s [line %(lineno)s]: %(message)s',
                        filemode='w',
                        level=logging.INFO)

    # FundPriceYear.work()
    # FundPriceMonth.work()
    # FundPriceWeek.work()
    # FundPriceDay.work()
    FundPriceDay.work_increase()
